<?

//Data loading
$title='REPORT SUMMARY ADVANCE';
if (GetParam("office","")) $perush = GetCompany (GetParam("office",""));
else $perush = 'PT. MASAJI TATANAN CONTAINER';

$page   = 'PAGE';
$TableName = "ais_advance";
$Result = ReportList ("", "", "");
if ($Result) {
	$Header = "bs_nobkt\tbs_uraian\tdebtur\tdebnam\tbs_jumlah\ttglbkt\tbkbb_nobkt\tbkbb_jumlah\tbkbb_tgl\tmm_nobkt\tmm_jumlah\tmm_tglbkt\tlebihkurang_nobkt\tlebihkurang_jumlah\tlebihkurang_tglbkt\n";
	foreach ($Result as $Row) {
		foreach ($Row as $Field) {
			if (strlen($strExcelRow)) $strExcelRow .= "\t$Field";
			else $strExcelRow = "$Field";
		}
		$strOutput .= $strExcelRow."\n";
		$strExcelRow="";
	}
	header("Content-type:application/vnd.notepad");
	header("Content-Disposition:filename=ais_advance_saldo.txt");
	print $Header.$strOutput;
	
}


function ReportList ($Space, $Index, $ParentID) {
global $RS,$Index,$TableName;
$DBConnection   = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
			if (!$Index) $Index = 0;
/*                        
                          $SQL = "
                                      SELECT $TableName.nobkt,$TableName.uraian,$TableName.debtur,$TableName.debnam, date_format($TableName.tglbkt,'%d/%m/%y') as tgl, sum($TableName.jumlah) as sumjumlah, ais_memorial.nobkt as entry3, ais_memorial.jumlah as amount3, IF (L.nobkt<>'',L.nobkt,IF(LZ.nobkt<>'',LZ.nobkt,'')) as entry4, IF (L.jumlah<>'',L.jumlah,IF(LZ.jumlah<>'',LZ.jumlah,'')) as amount4, bb_bk.jumlah as amount2
                                      FROM `$TableName` 
                                      LEFT JOIN 
                                      ais_memorial bb_bk ON $TableName.aslref=bb_bk.nobkt AND bb_bk.ktrasl=$TableName.ktrasl AND bb_bk.urut=999
                                      LEFT JOIN 
                                      ais_memorial ON bb_bk.nobkt=ais_memorial.noref AND ais_memorial.ktrasl=$TableName.ktrasl AND ais_memorial.urut=999
                                      LEFT JOIN
                                      ais_memorial L ON L.noref=ais_memorial.nobkt AND L.ktrasl=$TableName.ktrasl AND L.urut=999
                                      LEFT JOIN
                                      ais_memorial_z LZ ON LZ.noref=ais_memorial.nobkt AND LZ.ktrasl=$TableName.ktrasl AND LZ.urut=999
                                      WHERE 1
                                      
                                    ";
*/
$SQL = "
            SELECT $TableName.nobkt,$TableName.uraian,$TableName.debtur,$TableName.debnam, sum($TableName.jumlah) as sumjumlah, date_format($TableName.tglbkt,'%d/%m/%y') as tgl, 
            			 bb_bk.nobkt as aslref,
            			 bb_bk.jumlah as amount2, 
            			 bb_bk.tglbkt as bb_bk_tgl,
            			 ais_memorial.nobkt as entry3, 
            			 ais_memorial.jumlah as amount3, 
            			 ais_memorial.tglbkt as mmtglbkt,             			 
            			 IF (L.nobkt<>'',L.nobkt,IF(LZ.nobkt<>'',LZ.nobkt,'')) as entry4, 
            			 IF (L.jumlah<>'',L.jumlah,IF(LZ.jumlah<>'',LZ.jumlah,'')) as amount4,
									 IF (L.tglbkt<>'',L.tglbkt,IF(LZ.tglbkt<>'',LZ.tglbkt,'')) as tgl_lebihkurang
            			 
            FROM `$TableName` 
            INNER JOIN 
            ais_memorial bb_bk ON $TableName.nobkt=bb_bk.noref AND bb_bk.ktrasl=$TableName.ktrasl AND bb_bk.urut=999
            LEFT JOIN 
            ais_memorial ON bb_bk.nobkt=ais_memorial.noref AND ais_memorial.ktrasl=$TableName.ktrasl AND ais_memorial.urut=999 AND ais_memorial.nobkt like 'M%'
            LEFT JOIN
            ais_memorial L ON L.noref=bb_bk.nobkt AND L.ktrasl=bb_bk.ktrasl AND L.urut=999 AND L.nobkt not like 'M%'
            LEFT JOIN
            ais_memorial_z LZ ON LZ.noref=bb_bk.nobkt AND LZ.ktrasl=bb_bk.ktrasl AND LZ.urut=999  AND LZ.nobkt not like 'M%'
          ";
                                    
          $SQL .= " WHERE ais_memorial.nobkt IS NULL AND ".$TableName.".curr like '%".$curr."%' ";   
                        
          $SQL .= " GROUP BY $TableName.nobkt";
          $SQL .= " ORDER BY $TableName.tglbkt DESC ";
          
          $MyResult = $DBConnection->dbc->get_results($SQL,ARRAY_A);
          return $MyResult;
} 

class PDFReport extends PDF {

	function Header() {
		global $title,$perush,$page,$DEFINE_FONT;
		if ($DEFINE_FONT==0) {
			$this->AddFont('arialn','','arialn.php');
			$DEFINE_FONT=1;
		}
		$this->SetFont('arialn','',12);
                $this->Cell(195,5,$perush,0,1,'C',0);
                $this->Cell(195,5,$title,0,1,'C',0);
                $SystemDate = date("d/m/Y");
                $this->Cell(195,5,"REPORT DATE: ".$SystemDate,0,1,'C',0);
                $this->Cell(195,5,$page." ".$this->PageNo(),0,1,'R',0);
                $this->Line(10,30,205,30);
                $this->Ln();
                $this->Ln();
                //Header

                //Column widths
               $w=array(22,12,35,22,20,22,20,22,20);
                $this->SetFont('arialn','',8);
                $this->Cell($w[0],7,"TRANS NO",1,0,'C',0);
                $this->Cell($w[1],7,"DATE",1,0,'C',0);
                $this->Cell($w[2],7,"PAY TO",1,0,'C',0);

                $this->Cell($w[3],7,"BK/BB",1,0,'C',0);
                $this->Cell($w[4],7,"AMOUNT",1,0,'C',0);
                $this->Cell($w[5],7,"MEMORIAL",1,0,'C',0);
                $this->Cell($w[6],7,"AMOUNT",1,0,'C',0);
                $this->Cell($w[7],7,"CASH/BANK",1,0,'C',0);
                $this->Cell($w[8],7,"AMOUNT",1,0,'C',0);
                $this->Ln();    	
	}

	function ReportTable($Result) {
	    //Column widths
	    $w=array(22,12,35,22,20,22,20,22,20);
	    //Data
	    foreach($Result as $row)
	    {
                $no++;
	        $this->Cell($w[0],6,$row["nobkt"],'LR');
                $this->Cell($w[1],6,$row["tgl"],'LR');
                if (strlen($row["debnam"])>20) $row["debnam"] = substr($row["debnam"], 0, 20); 
	        $this->Cell($w[2],6,$row["debnam"],'LR');

	        $this->Cell($w[3],6,$row["aslref"],'LR',0,'R');
                $this->Cell($w[4],6,number_format($row["amount2"],2),'LR',0,'R');
                $this->Cell($w[5],6,$row["entry3"],'LR',0,'R');
                $this->Cell($w[6],6,number_format($row["amount3"],2),'LR',0,'R');
                $this->Cell($w[7],6,$row["entry4"],'LR',0,'R');
                $this->Cell($w[8],6,number_format($row["amount4"],2),'LR',0,'R');
	        $this->Ln();
	    }
	    //Closure line
	    //$this->Cell(array_sum($w),0,'','T');
	}

	function Footer() {
	    //Closure line
	    $this->Cell(195,0,'','T');
	}

}


?>
